﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using AtlasPlantasMedicinales.Models;
using MySql.Data.MySqlClient;
using System.Web.Helpers;
using System.IO;

namespace AtlasPlantasMedicinales.Controllers
{
    public class ArticuloCientificoController : Controller
    {
        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;
        //
        // GET: /ArticuloCientifico/

        private ArticuloCientifico GetACInicial() {
            var model = new ArticuloCientifico();
            var selectedDept = new List<DistribucionGeografica>();
            model.departamentos = DepartamentosRepository.GetAll().ToList();
            model.departamentosSelect = selectedDept;
            return model;
        }

        private ArticuloCientifico GetACModel(ArticuloCientifico AC) 
        {
            AC.departamentos = DepartamentosRepository.GetAll().ToList();
            int n = AC.PostedDepartamentos.IdsDepartamentos.Length;
            for (int i = 0; i < n; i++)
                AC.departamentos[Convert.ToInt16(AC.PostedDepartamentos.IdsDepartamentos[i])-1].IsSelected = true;
            return AC;
        }

        [HttpGet]
        public ActionResult AgregarPlanta()
        {
            ViewBag.autenticado = TempData["IS"];
            TempData["IS"] = ViewBag.autenticado;
            ViewBag.categoria = TempData["Categoria"];
            TempData["Categoria"] = ViewBag.categoria;
            ViewBag.NombreUsuario = TempData["NombreUsuario"];
            TempData["NombreUsuario"] = ViewBag.NombreUsuario;
            ViewBag.Usuario_objeto = TempData["Usuario_objeto"];
            TempData["Usuario_objeto"] = ViewBag.Usuario_objeto;
            ArticuloCientifico AC = new ArticuloCientifico();
            PlantaDeInteres PdI = new PlantaDeInteres();
            PruebaDePureza PdP = new PruebaDePureza();
            EnsayoQuimico EQ = new EnsayoQuimico();
            UsoMedicinal UM = new UsoMedicinal();
            LinkPadecimiento LP = new LinkPadecimiento();
            Farmacologia F = new Farmacologia();
            return View(GetACInicial());
        }


        [HttpPost]
        public ActionResult EliminarPlanta(ArticuloCientifico AC)
        {
            PlantaDeInteres PdI = AC.PlantaInteres;
            PruebaDePureza PdP = AC.PruebaPureza;
            EnsayoQuimico EQ = AC.Ensayo;
            UsoMedicinal UM = AC.UsoMed;
            LinkPadecimiento LP = AC.UsoMed.LinkPad;
            Farmacologia F = AC.Farma;
            ViewBag.autenticado = TempData["IS"];
            TempData["IS"] = ViewBag.autenticado;
            ViewBag.categoria = TempData["Categoria"];
            TempData["Categoria"] = ViewBag.categoria;
            ViewBag.NombreUsuario = TempData["NombreUsuario"];
            TempData["NombreUsuario"] = ViewBag.NombreUsuario;
            ViewBag.Usuario_objeto = TempData["Usuario_objeto"];
            TempData["Usuario_objeto"] = ViewBag.Usuario_objeto;
            int cantidad_dep = AC.PostedDepartamentos.IdsDepartamentos.Length;


            server = "localhost";
            database = "atlas_de_plantas_medicinales";
            uid = "root";
            password = "123456";
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" +
            database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            String query;

            //-----------------------------FOREIGN KEYS-----------------------------------------
            connection = new MySqlConnection(connectionString);
            connection.Open();

            query = "SET FOREIGN_KEY_CHECKS=0;";
            MySqlCommand insert = new MySqlCommand(query, connection);
            insert.ExecuteNonQuery();
            connection.Close();

            //-----------------------------Plantas-----------------------------------------
            connection = new MySqlConnection(connectionString);
            connection.Open();

            query = "DELETE FROM Plantas WHERE ID ='" + AC.ID + "'";
            insert = new MySqlCommand(query, connection);
            insert.ExecuteNonQuery();
            connection.Close();

            //-----------------------------Plantas y usuarios-----------------------------------------
            connection = new MySqlConnection(connectionString);
            connection.Open();

            query = "DELETE FROM plantas_has_usuarios WHERE Plantas_ID ='" + AC.ID + "'";
            insert = new MySqlCommand(query, connection);
            insert.ExecuteNonQuery();
            connection.Close();

            //-----------------------------Planta de Interes-----------------------------------------



            connection = new MySqlConnection(connectionString);
            connection.Open();

            query = "DELETE FROM PlantaDeInteres WHERE ID='" + PdI.ID + "'";
            insert = new MySqlCommand(query, connection);
            insert.ExecuteNonQuery();
            connection.Close();


            //----------------------------------PruebaDePureza------------------------------------------
            connection = new MySqlConnection(connectionString);
            connection.Open();

            query = "DELETE FROM PruebaDePureza WHERE ID='" + PdP.ID + "'";
            insert = new MySqlCommand(query, connection);
            insert.ExecuteNonQuery();
            connection.Close();


            //--------------------------------------EnsayoQuimico---------------------------------------
            connection = new MySqlConnection(connectionString);
            connection.Open();

            query = "DELETE FROM EnsayoQuimico WHERE ID='" + EQ.ID + "'";
            insert = new MySqlCommand(query, connection);
            insert.ExecuteNonQuery();
            connection.Close();



            //-----------------------------------LinkPadecimiento-------------------
            connection = new MySqlConnection(connectionString);
            connection.Open();

            query = "DELETE FROM LinkPadecimiento WHere ID='" + LP.ID + "'";
            insert = new MySqlCommand(query, connection);
            insert.ExecuteNonQuery();
            connection.Close();


            //-------------------------------UsoMedicinal----------------------------------
            connection = new MySqlConnection(connectionString);
            connection.Open();

            query = "DELETE FROM UsoMedicinal WHERE ID='" + UM.ID + "'";
            insert = new MySqlCommand(query, connection);
            insert.ExecuteNonQuery();
            connection.Close();


            //----------------------------------Farmacologia---------------------------
            connection = new MySqlConnection(connectionString);
            connection.Open();

            query = "DELETE FROM Farmacologia WHERE ID='" + F.ID + "'";
            insert = new MySqlCommand(query, connection);
            insert.ExecuteNonQuery();
            connection.Close();


            



            return RedirectToAction("Index", "Home");
        }

        [HttpPost]
        public ActionResult EditarPlanta(ArticuloCientifico AC)
        {
            PlantaDeInteres PdI = AC.PlantaInteres;
            PruebaDePureza PdP = AC.PruebaPureza;
            EnsayoQuimico EQ = AC.Ensayo;
            UsoMedicinal UM = AC.UsoMed;
            LinkPadecimiento LP = AC.UsoMed.LinkPad;
            Farmacologia F = AC.Farma;
            ViewBag.autenticado = TempData["IS"];
            TempData["IS"] = ViewBag.autenticado;
            ViewBag.categoria = TempData["Categoria"];
            TempData["Categoria"] = ViewBag.categoria;
            ViewBag.NombreUsuario = TempData["NombreUsuario"];
            TempData["NombreUsuario"] = ViewBag.NombreUsuario;
            ViewBag.Usuario_objeto = TempData["Usuario_objeto"];
            TempData["Usuario_objeto"] = ViewBag.Usuario_objeto;
            int cantidad_dep = AC.PostedDepartamentos.IdsDepartamentos.Length;
            if (ModelState.IsValid && cantidad_dep != 0)
            {
                string departamentos = "";
                for (int i = 0; i < cantidad_dep - 1; i++)
                    departamentos += AC.PostedDepartamentos.IdsDepartamentos[i] + ',';
                departamentos += AC.PostedDepartamentos.IdsDepartamentos[cantidad_dep - 1];


                //FOTO IMAGEN DE PLANTA
                HttpPostedFileWrapper file = AC.ImagenPath;
                //Este pic habria que modificarlo para darle el nombre que queramos de las imagenes
                string pic = System.IO.Path.GetFileName(file.FileName);
                string pathPlanta = System.IO.Path.Combine(
                                        Server.MapPath("~/Home/Imagenes/"), pic);
                // file is uploaded
                file.SaveAs(pathPlanta);
                pathPlanta = "Imagenes/" + pic;
                // save the image path path to the database or you can send image
                // directly to database
                // in-case if you want to store byte[] ie. for DB
                using (MemoryStream ms = new MemoryStream())
                {
                    file.InputStream.CopyTo(ms);
                    byte[] array = ms.GetBuffer();
                }

                //FOTO IMAGEN DE PLANTA
                HttpPostedFileWrapper fileEQ = AC.Ensayo.PathImagen;
                //Este pic habria que modificarlo para darle el nombre que queramos de las imagenes
                string picEQ = System.IO.Path.GetFileName(fileEQ.FileName);
                string pathEQ = System.IO.Path.Combine(
                                        Server.MapPath("~/Home/Imagenes/"), picEQ);
                // file is uploaded
                fileEQ.SaveAs(pathEQ);
                pathEQ = "Imagenes/" + picEQ;
                // save the image path path to the database or you can send image
                // directly to database
                // in-case if you want to store byte[] ie. for DB
                using (MemoryStream ms = new MemoryStream())
                {
                    fileEQ.InputStream.CopyTo(ms);
                    byte[] array = ms.GetBuffer();
                }

                

                server = "localhost";
                database = "atlas_de_plantas_medicinales";
                uid = "root";
                password = "123456";
                string connectionString;
                connectionString = "SERVER=" + server + ";" + "DATABASE=" +
                database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

                String query;
                
                

                //-----------------------------Planta de Interes-----------------------------------------
               


                connection = new MySqlConnection(connectionString);
                connection.Open();

                query = "UPDATE PlantaDeInteres SET AparienciaGeneral='" + PdI.AparienciaGeneral + "', PropOrganolepticas='" + PdI.PropOrganolepticas + "', CaracteristicasMicro='" + PdI.CaracteristicasMicro + "', MaterialVegEnPolvo='" + PdI.MaterialVegEnPolvo + "' WHERE ID = '" + PdI.ID + "'";
                MySqlCommand insert = new MySqlCommand(query, connection);
                insert.ExecuteNonQuery();
                ModelState.AddModelError("", "Se ha actualizado la planta con éxito");
                connection.Close();


                //----------------------------------PruebaDePureza------------------------------------------
                connection = new MySqlConnection(connectionString);
                connection.Open();
                query = "UPDATE PruebaDePureza SET Microbiologicas='" + PdP.Microbiologicas + "', MateriaOrgExtrana='" + PdP.MateriaOrgExtrana + "', CenizasTotales='" + PdP.CenizasTotales + "', CenizasInsolEnAcido='" + PdP.CenizasInsolEnAcido + "', CenizasSulfatas='" + PdP.CenizasSulfatas + "', PerdidaPorSecado='" + PdP.PerdidaPorSecado + "', ResiduoDePesticidas='" + PdP.ResiduosDePesticidas + "', MetalesPesados='" + PdP.MetalesPesados + "', ResiduosRadiactivos='" + PdP.ResiduosRadiactivos + "', OtrasPruebasPureza='" + PdP.OtrasPruebasPureza + "' WHERE ID = '"+PdP.ID+"'";
                insert = new MySqlCommand(query, connection);
                insert.ExecuteNonQuery();
                ModelState.AddModelError("", "Se ha actualizado la planta con éxito");
                connection.Close();


                //--------------------------------------EnsayoQuimico---------------------------------------
                connection = new MySqlConnection(connectionString);
                connection.Open();
                query = "UPDATE EnsayoQuimico SET MetabolitoSecundario='" + EQ.MetabolitoSecundario + "', ConstQuimMay='" + EQ.ConstQuimMay + "', PathImagen='" + pathEQ + "' WHERE ID = '"+EQ.ID+"'";
                insert = new MySqlCommand(query, connection);
                insert.ExecuteNonQuery();
                ModelState.AddModelError("", "Se ha actualizado la planta con éxito");
                connection.Close();



                //-----------------------------------LinkPadecimiento-------------------
                connection = new MySqlConnection(connectionString);
                connection.Open();
                query = "UPDATE LinkPadecimiento SET Link='" + LP.Link + "', Definicion='" + LP.Definicion + "', Descripcion='" + LP.Descripcion + "', CausaSintoma='" + LP.CausaSintoma + "', Diagnostico='" + LP.Diagnostico + "', Tratamiento='" + LP.Tratamiento + "', Prevencion='" + LP.Prevencion + "', Esquema='" + LP.Esquema + "' WHERE ID = '"+LP.ID+"'";
                insert = new MySqlCommand(query, connection);
                insert.ExecuteNonQuery();
                ModelState.AddModelError("", "Se ha actualizado la planta con éxito");
                connection.Close();


                //-------------------------------UsoMedicinal----------------------------------
                connection = new MySqlConnection(connectionString);
                connection.Open();
                query = "UPDATE UsoMedicinal SET UsoSisTradicional='" +UM.UsoSisTradicional+ "', UsoSisTradicionalNoVal='" +UM.UsoSisTradicionalNoVal+  "' WHERE ID = '"+UM.ID+"'";
                insert = new MySqlCommand(query, connection);
                insert.ExecuteNonQuery();
                ModelState.AddModelError("", "Se ha actualizado la planta con éxito");
                connection.Close();


                //----------------------------------Farmacologia---------------------------
                connection = new MySqlConnection(connectionString);
                connection.Open();
                query = "UPDATE Farmacologia SET ActividadFarmacologica='" + F.ActividadFarmacologica + "', ActividadMiscelanea='" + F.ActividadMiscelanea + "', FarmacologiaClinica='" + F.FarmacologiaClinica + "' WHERE ID = '"+F.ID+"'";
                insert = new MySqlCommand(query, connection);
                insert.ExecuteNonQuery();
                ModelState.AddModelError("", "Se ha actualizado la planta con éxito");
                connection.Close();


                //-----------------------------Plantas-----------------------------------------
                connection = new MySqlConnection(connectionString);
                connection.Open();
                int valor = 0;
                if (AC.comoBorrador)
                    valor = 1;
                int valor1 = 0;
                if (AC.Habilitado)
                    valor1 = 1;

                query = "UPDATE Plantas SET NombreCientifico ='" + AC.NombreCientifico + "', Familia='" + AC.Familia + "', Definicion='" + AC.Definicion + "', Sinonimo='" + AC.Sinonimo + "', NombresVernaculares='" + AC.NombreVernacular + "', DistribucionGeo='" + departamentos + "', Descripcion='" + AC.Descripcion + "',  Contraindicacion='" + AC.Contraindicacion + "', ListaPrecauciones='" + AC.ListaPrecauciones + "', ReaccionAdversa='" + AC.ReaccionAdversa + "', FormaDosificacion='" + AC.FormaDosificacion + "', Posologia='" + AC.Posologia + "', Referencias='" + AC.Referencias + "', PathFotoPlanta='" + pathPlanta + "', Borrador='" + valor + "', Habilitada='" + valor1 + "' WHERE ID = '" + AC.ID + "'";
                insert = new MySqlCommand(query, connection);
                insert.ExecuteNonQuery();
                ModelState.AddModelError("", "Se ha actualizado la planta con éxito");
                connection.Close();


                return RedirectToAction("Index", "Home");
            }
            else
            {
                if (cantidad_dep == 0)
                    ModelState.AddModelError("", "Debe seleccionar almenos un departamento");
                ModelState.AddModelError("", "Los datos ingresados son incorrectos.");
                return View(GetACModel(AC));
            }


        }    

        [HttpPost]
        public ActionResult datosPlanta()
        {
            ArticuloCientifico AC = (ArticuloCientifico) TempData["datosParaLaPlanta"];
            return View("EditarPlanta",AC);
        }


        [HttpPost]
        public ActionResult AgregarPlanta(ArticuloCientifico AC)
        {
            PlantaDeInteres PdI = AC.PlantaInteres;
            PruebaDePureza PdP = AC.PruebaPureza;
            EnsayoQuimico EQ = AC.Ensayo;
            UsoMedicinal UM = AC.UsoMed;
            LinkPadecimiento LP = AC.UsoMed.LinkPad;
            Farmacologia F = AC.Farma;

            ViewBag.autenticado = TempData["IS"];
            TempData["IS"] = ViewBag.autenticado;
            ViewBag.categoria = TempData["Categoria"];
            TempData["Categoria"] = ViewBag.categoria;
            ViewBag.NombreUsuario = TempData["NombreUsuario"];
            TempData["NombreUsuario"] = ViewBag.NombreUsuario;
            ViewBag.Usuario_objeto = TempData["Usuario_objeto"];
            TempData["Usuario_objeto"] = ViewBag.Usuario_objeto;
            int cantidad_dep = AC.PostedDepartamentos.IdsDepartamentos.Length;
            if (ModelState.IsValid && cantidad_dep!=0)
            {
                string departamentos = "";
                for (int i = 0; i < cantidad_dep - 1; i++)
                    departamentos += AC.PostedDepartamentos.IdsDepartamentos[i]+',';
                departamentos += AC.PostedDepartamentos.IdsDepartamentos[cantidad_dep - 1];

                
                //FOTO IMAGEN DE PLANTA
                HttpPostedFileWrapper file = AC.ImagenPath;
                //Este pic habria que modificarlo para darle el nombre que queramos de las imagenes
                string pic = System.IO.Path.GetFileName(file.FileName);
                string pathPlanta = System.IO.Path.Combine(
                                        Server.MapPath("~/Home/Imagenes/"), pic);
                // file is uploaded
                file.SaveAs(pathPlanta);
                pathPlanta = "Imagenes/" + pic;
                // save the image path path to the database or you can send image
                // directly to database
                // in-case if you want to store byte[] ie. for DB
                using (MemoryStream ms = new MemoryStream())
                {
                    file.InputStream.CopyTo(ms);
                    byte[] array = ms.GetBuffer();
                }

                //FOTO IMAGEN DE PLANTA
                HttpPostedFileWrapper fileEQ = AC.Ensayo.PathImagen;
                //Este pic habria que modificarlo para darle el nombre que queramos de las imagenes
                string picEQ = System.IO.Path.GetFileName(fileEQ.FileName);
                string pathEQ = System.IO.Path.Combine(
                                        Server.MapPath("~/Home/Imagenes/"), picEQ);
                // file is uploaded
                fileEQ.SaveAs(pathEQ);
                pathEQ = "Imagenes/" + picEQ;
                // save the image path path to the database or you can send image
                // directly to database
                // in-case if you want to store byte[] ie. for DB
                using (MemoryStream ms = new MemoryStream())
                {
                    fileEQ.InputStream.CopyTo(ms);
                    byte[] array = ms.GetBuffer();
                }

                int PlantaDeInteres_ID = 0;
                int PruebaDePureza_ID = 0;
                int EnsayoQuimico_ID = 0;
                int UsoMedicinal_ID = 0;
                int Farmacologia_ID = 0;
                int LinkPadecimiento_ID = 0;
                int Plantas_ID = 0;
                int usuarios_ID = 0;

                server = "localhost";
                database = "atlas_de_plantas_medicinales";
                uid = "root";
                password = "123456";
                string connectionString;
                connectionString = "SERVER=" + server + ";" + "DATABASE=" +
                database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
                connection = new MySqlConnection(connectionString);
                connection.Open();
                String query = "SELECT * FROM Plantas WHERE NombreCientifico='" + AC.NombreCientifico + "'";
                MySqlCommand comando = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = comando.ExecuteReader();
                String id = "";
                while (dataReader.Read())
                {
                    id = (String)dataReader["NombreCientifico"];
                }
                connection.Close();
                connection = new MySqlConnection(connectionString);
                connection.Open();
                if (id.Equals(""))
                {
                    //-----------------------------Planta de Interes-----------------------------------------
                    query = "INSERT INTO PlantaDeInteres (AparienciaGeneral, PropOrganolepticas, CaracteristicasMicro, MaterialVegEnPolvo) VALUES ('" + PdI.AparienciaGeneral + "','" + PdI.PropOrganolepticas + "','" + PdI.CaracteristicasMicro + "','" + PdI.MaterialVegEnPolvo + "')";
                    MySqlCommand insert = new MySqlCommand(query, connection);
                    insert.ExecuteNonQuery();
                    ModelState.AddModelError("", "Se ha agregado la planta con éxito");
                    connection.Close();

                    connection = new MySqlConnection(connectionString);
                    connection.Open();
                    query = "SELECT * FROM PlantaDeInteres WHERE AparienciaGeneral = '" + AC.PlantaInteres.AparienciaGeneral + "'";
                    MySqlCommand select = new MySqlCommand(query, connection);
                    dataReader = select.ExecuteReader();
                    while (dataReader.Read())
                    {
                        PlantaDeInteres_ID = (int)dataReader["ID"];
                    }
                    connection.Close();

                    //----------------------------------PruebaDePureza------------------------------------------
                    connection = new MySqlConnection(connectionString);
                    connection.Open();
                    query = "INSERT INTO PruebaDePureza (Microbiologicas, MateriaOrgExtrana, CenizasTotales, CenizasInsolEnAcido, CenizasSulfatas, PerdidaPorSecado, ResiduoDePesticidas, MetalesPesados, ResiduosRadiactivos, OtrasPruebasPureza) VALUES ('" + PdP.Microbiologicas + "','" + PdP.MateriaOrgExtrana + "','" + PdP.CenizasTotales + "','" + PdP.CenizasInsolEnAcido + "','" + PdP.CenizasSulfatas + "','" + PdP.PerdidaPorSecado + "','" + PdP.ResiduosDePesticidas + "','" + PdP.MetalesPesados + "','" + PdP.ResiduosRadiactivos + "','" + PdP.OtrasPruebasPureza + "')";
                    insert = new MySqlCommand(query, connection);
                    insert.ExecuteNonQuery();
                    ModelState.AddModelError("", "Se ha agregado la planta con éxito");
                    connection.Close();

                    connection = new MySqlConnection(connectionString);
                    connection.Open();
                    query = "SELECT * FROM PruebaDePureza WHERE Microbiologicas = '" + AC.PruebaPureza.Microbiologicas + "'";
                    select = new MySqlCommand(query, connection);
                    dataReader = select.ExecuteReader();
                    while (dataReader.Read())
                    {
                        PruebaDePureza_ID = (int)dataReader["ID"];
                    }
                    connection.Close();

                    //--------------------------------------EnsayoQuimico---------------------------------------
                    connection = new MySqlConnection(connectionString);
                    connection.Open();
                    query = "INSERT INTO EnsayoQuimico (MetabolitoSecundario, ConstQuimMay, PathImagen) VALUES ('" + EQ.MetabolitoSecundario + "','" + EQ.ConstQuimMay + "','" + pathEQ + "')";
                    insert = new MySqlCommand(query, connection);
                    insert.ExecuteNonQuery();
                    ModelState.AddModelError("", "Se ha agregado la planta con éxito");
                    connection.Close();

                    connection = new MySqlConnection(connectionString);
                    connection.Open();
                    query = "SELECT * FROM EnsayoQuimico WHERE MetabolitoSecundario = '" + AC.Ensayo.MetabolitoSecundario + "'";
                    select = new MySqlCommand(query, connection);
                    dataReader = select.ExecuteReader();
                    while (dataReader.Read())
                    {
                        EnsayoQuimico_ID = (int)dataReader["ID"];
                    }
                    connection.Close();

                    //-----------------------------------LinkPadecimiento-------------------
                    connection = new MySqlConnection(connectionString);
                    connection.Open();
                    query = "INSERT INTO LinkPadecimiento (Link, Definicion, Descripcion, CausaSintoma, Diagnostico, Tratamiento, Prevencion, Esquema) VALUES ('" + LP.Link + "','" + LP.Definicion + "','" + LP.Descripcion + "','" + LP.CausaSintoma + "','" + LP.Diagnostico + "','" + LP.Tratamiento + "','" + LP.Prevencion + "','" + LP.Esquema + "')";
                    insert = new MySqlCommand(query, connection);
                    insert.ExecuteNonQuery();
                    ModelState.AddModelError("", "Se ha agregado la planta con éxito");
                    connection.Close();

                    connection = new MySqlConnection(connectionString);
                    connection.Open();
                    query = "SELECT * FROM LinkPadecimiento WHERE Definicion = '" + AC.UsoMed.LinkPad.Definicion + "'";
                    select = new MySqlCommand(query, connection);
                    dataReader = select.ExecuteReader();
                    while (dataReader.Read())
                    {
                        LinkPadecimiento_ID = (int)dataReader["ID"];
                    }
                    connection.Close();

                    //-------------------------------UsoMedicinal----------------------------------
                    connection = new MySqlConnection(connectionString);
                    connection.Open();
                    query = "INSERT INTO UsoMedicinal (UsoSisTradicional, UsoSisTradicionalNoVal, LinkPadecimiento_ID) VALUES ('" + UM.UsoSisTradicional + "','" + UM.UsoSisTradicionalNoVal + "','" + LinkPadecimiento_ID + "')";
                    insert = new MySqlCommand(query, connection);
                    insert.ExecuteNonQuery();
                    ModelState.AddModelError("", "Se ha agregado la planta con éxito");
                    connection.Close();

                    connection = new MySqlConnection(connectionString);
                    connection.Open();
                    query = "SELECT * FROM UsoMedicinal WHERE UsoSisTradicional = '" + AC.UsoMed.UsoSisTradicional + "'";
                    select = new MySqlCommand(query, connection);
                    dataReader = select.ExecuteReader();
                    while (dataReader.Read())
                    {
                        UsoMedicinal_ID = (int)dataReader["ID"];
                    }
                    connection.Close();

                    //----------------------------------Farmacologia---------------------------
                    connection = new MySqlConnection(connectionString);
                    connection.Open();
                    query = "INSERT INTO Farmacologia (ActividadFarmacologica, ActividadMiscelanea, FarmacologiaClinica) VALUES ('" + F.ActividadFarmacologica + "','" + F.ActividadMiscelanea + "','" + F.FarmacologiaClinica + "')";
                    insert = new MySqlCommand(query, connection);
                    insert.ExecuteNonQuery();
                    ModelState.AddModelError("", "Se ha agregado la planta con éxito");
                    connection.Close();

                    connection = new MySqlConnection(connectionString);
                    connection.Open();
                    query = "SELECT * FROM Farmacologia WHERE ActividadFarmacologica = '" + AC.Farma.ActividadFarmacologica + "'";
                    select = new MySqlCommand(query, connection);
                    dataReader = select.ExecuteReader();
                    while (dataReader.Read())
                    {
                        Farmacologia_ID = (int)dataReader["ID"];
                    }
                    connection.Close();

                    //-----------------------------Plantas-----------------------------------------
                    int valor = 0;
                    if (AC.comoBorrador)
                        valor = 1;
                    int valor1 = 0;
                    if (AC.Habilitado)
                        valor1 = 1;
                    connection = new MySqlConnection(connectionString);
                    connection.Open();
                    query = "INSERT INTO Plantas (NombreCientifico, Familia, Definicion, Sinonimo, NombresVernaculares, DistribucionGeo, Descripcion, PlantaDeInteres_ID, PruebasIdentGeneral, PruebaDePureza_ID, EnsayoQuimico_ID, UsoMedicinal_ID, Farmacologia_ID, Contraindicacion, ListaPrecauciones, ReaccionAdversa, FormaDosificacion, Posologia, Referencias, PathFotoPlanta,Borrador,Habilitada) VALUES ('" + AC.NombreCientifico + "','" + AC.Familia + "','" + AC.Definicion + "','" + AC.Sinonimo + "','" + AC.NombreVernacular + "','" + departamentos + "','" + AC.Descripcion + "','" + PlantaDeInteres_ID + "','" + AC.PruebasIdentGeneral + "','" + PruebaDePureza_ID + "','" + EnsayoQuimico_ID + "','" + UsoMedicinal_ID + "','" + Farmacologia_ID + "','" + AC.Contraindicacion + "','" + AC.ListaPrecauciones + "','" + AC.ReaccionAdversa + "','" + AC.FormaDosificacion + "','" + AC.Posologia + "','" + AC.Referencias + "','" + pathPlanta + "','" + valor + "','" + valor1 + "')";
                    insert = new MySqlCommand(query, connection);
                    insert.ExecuteNonQuery();
                    ModelState.AddModelError("", "Se ha agregado la planta con éxito");
                    connection.Close();

                    connection = new MySqlConnection(connectionString);
                    connection.Open();
                    query = "SELECT * FROM Plantas WHERE NombreCientifico = '" + AC.NombreCientifico + "'";
                    select = new MySqlCommand(query, connection);
                    dataReader = select.ExecuteReader();
                    while (dataReader.Read())
                    {
                        Plantas_ID = (int)dataReader["ID"];
                    }
                    connection.Close();

                    //-------------------------------Plantas_has_usuarios--------------------------
                    usuarios_ID = ViewBag.Usuario_objeto.ID;
                    connection = new MySqlConnection(connectionString);
                    connection.Open();
                    query = "INSERT INTO Plantas_has_usuarios (Plantas_ID, usuarios_ID) VALUES ('" + Plantas_ID + "','" + usuarios_ID + "')";
                    insert = new MySqlCommand(query, connection);
                    insert.ExecuteNonQuery();
                    ModelState.AddModelError("", "Se ha agregado la planta con éxito");
                    connection.Close();
                    
                }
                else
                {
                    ModelState.AddModelError("", "La planta a agregar ya existe.");
                    return View(GetACModel(AC));
                }
                connection.Close();
                return RedirectToAction("Index", "Home");
            }
            else
            {   
                if (cantidad_dep==0)
                    ModelState.AddModelError("", "Debe seleccionar almenos un departamento");
                ModelState.AddModelError("", "Los datos ingresados son incorrectos.");
                return View(GetACModel(AC));
            }
        }
    }
}
